package com.dcivision.dms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.impl.LogFactoryImpl;

import com.dcivision.dms.bean.DmsDocument;
import com.dcivision.dms.bean.DmsTreeNode;
import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.PermissionManager;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.SystemParameterConstant;
import com.dcivision.framework.SystemParameterFactory;
import com.dcivision.framework.Utility;

public class DmsPermissionDAObject {

  protected SessionContainer sessionContainer = null;
  protected Connection dbConn = null;
  protected Log log = new LogFactoryImpl().getInstance(this.getClass());
  
  public DmsPermissionDAObject() {
  }
  
  public DmsPermissionDAObject(SessionContainer sessionContainer, Connection dbConn) {
    this();
    this.sessionContainer = sessionContainer;
    this.dbConn = dbConn;
  }
  
  public Integer getDocumentParentId(Integer documentId) throws ApplicationException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = dbConn.prepareStatement("SELECT PARENT_ID FROM DMS_DOCUMENT WHERE ID=?");
      pstmt.setInt(1, documentId.intValue());
      rs = pstmt.executeQuery();
      if (rs.next()) {
        return new Integer(rs.getString("PARENT_ID"));
      }else {
        return null;
      }
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
    } catch (Exception e) {
      log.error(e, e);
      throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
    } finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
  
  public Map getDocumentIdPidMap(List documentIds) throws ApplicationException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = dbConn.prepareStatement("SELECT ID, PARENT_ID FROM DMS_DOCUMENT WHERE "+this.getInSQL("ID",documentIds));
      rs = pstmt.executeQuery();
      
      Map result = new HashMap();
      while (rs.next()) {
        result.put(new Integer(rs.getString("ID")), new Integer(rs.getString("PARENT_ID")));
      }
      return result;
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
    } catch (Exception e) {
      log.error(e, e);
      throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
    } finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
  
  public boolean isForbiddenPermissionWhichNotInMatchRequired(
      String objectType, 
      Integer objectId,
      List userRoleIds,
      List userGroupIds,
      Integer userRecordId) throws ApplicationException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      StringBuffer unionSQL = new StringBuffer();
      
      unionSQL.append("SELECT distinct OBJECT_ID ");
      unionSQL.append("FROM   USER_ROLE_PERMISSION ");
      unionSQL.append("WHERE MUST_FLAG='Y' AND OBJECT_TYPE='"+objectType+"' ");
      unionSQL.append("AND OBJECT_ID = "+objectId+" ");
      if (userRoleIds != null && userRoleIds.size() > 0) {
        unionSQL.append("AND ("+this.getNotInSQL("USER_ROLE_ID", userRoleIds) + ") ");
      }
      unionSQL.append(" UNION ");
      unionSQL.append("SELECT distinct OBJECT_ID ");
      unionSQL.append("FROM   USER_GROUP_PERMISSION ");
      unionSQL.append("WHERE MUST_FLAG='Y' AND OBJECT_TYPE='"+objectType+"' ");
      unionSQL.append("AND OBJECT_ID = "+objectId+" ");
      if (userGroupIds != null && userGroupIds.size() > 0) {
        unionSQL.append("AND ("+this.getNotInSQL("USER_GROUP_ID", userGroupIds) + ") ");
      }
      unionSQL.append(" UNION ");
      unionSQL.append("SELECT distinct OBJECT_ID ");
      unionSQL.append("FROM   USER_RECORD_PERMISSION ");
      unionSQL.append("WHERE MUST_FLAG='Y' AND OBJECT_TYPE='"+objectType+"' ");
      unionSQL.append("AND OBJECT_ID = "+objectId+" ");
      unionSQL.append("AND USER_RECORD_ID NOT IN (" + userRecordId.toString() + ") ");
      unionSQL.append("ORDER BY OBJECT_ID");

      pstmt = dbConn.prepareStatement(unionSQL.toString());
      rs = pstmt.executeQuery();
      
      if (rs.next()) {
        return true;
      }else {
        return false;
      }
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
    } catch (Exception e) {
      log.error(e, e);
      throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
    } finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
  
  public Map getForbiddenPermissionMapWhichNotInMatchRequired(
      String objectType, 
      List objectIds,
      List userRoleIds,
      List userGroupIds,
      Integer userRecordId) throws ApplicationException {
    Map hmResult = new HashMap();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      StringBuffer unionSQL = new StringBuffer();
      
      unionSQL.append("SELECT distinct OBJECT_ID ");
      unionSQL.append("FROM   USER_ROLE_PERMISSION ");
      unionSQL.append("WHERE MUST_FLAG='Y' AND OBJECT_TYPE='"+objectType+"' ");
      unionSQL.append("AND ("+this.getInSQL("OBJECT_ID",objectIds) + ") ");
      if (userRoleIds != null && userRoleIds.size() > 0) {
        unionSQL.append("AND ("+this.getNotInSQL("USER_ROLE_ID", userRoleIds) + ") ");
      }
      unionSQL.append(" UNION ");
      unionSQL.append("SELECT distinct OBJECT_ID ");
      unionSQL.append("FROM   USER_GROUP_PERMISSION ");
      unionSQL.append("WHERE MUST_FLAG='Y' AND OBJECT_TYPE='"+objectType+"' ");
      unionSQL.append("AND ("+this.getInSQL("OBJECT_ID",objectIds) + ") ");
      if (userGroupIds != null && userGroupIds.size() > 0) {
        unionSQL.append("AND ("+this.getNotInSQL("USER_GROUP_ID", userGroupIds) + ") ");
      }
      unionSQL.append(" UNION ");
      unionSQL.append("SELECT distinct OBJECT_ID ");
      unionSQL.append("FROM   USER_RECORD_PERMISSION ");
      unionSQL.append("WHERE MUST_FLAG='Y' AND OBJECT_TYPE='"+objectType+"' ");
      unionSQL.append("AND ("+this.getInSQL("OBJECT_ID", objectIds) + ") ");
      unionSQL.append("AND USER_RECORD_ID NOT IN (" + userRecordId.toString() + ") ");
      unionSQL.append("ORDER BY OBJECT_ID");

      pstmt = dbConn.prepareStatement(unionSQL.toString());
      rs = pstmt.executeQuery();
      
      while (rs.next()) {
        hmResult.put(objectType+""+rs.getInt("OBJECT_ID"), PermissionManager.DENIED_READ_PERMISSION);
      }
      return hmResult;
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
    } catch (Exception e) {
      log.error(e, e);
      throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
    } finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
  
  public String getDirectPermission(
      String objectType, 
      Integer objectId,
      List userRoleIds,
      List userGroupIds,
      Integer userRecordId) throws ApplicationException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      StringBuffer unionSQL = new StringBuffer();
      boolean hasAppend = false;
      if (userRoleIds != null && userRoleIds.size() > 0) {
        unionSQL.append("SELECT OBJECT_ID, PERMISSION ");
        unionSQL.append("FROM   USER_ROLE_PERMISSION ");
        unionSQL.append("WHERE OBJECT_TYPE='"+objectType+"' ");
        unionSQL.append("AND OBJECT_ID=" + objectId + " ");
        unionSQL.append("AND ("+this.getInSQL("USER_ROLE_ID", userRoleIds) + ") ");
        hasAppend = true;
      }

      if (userGroupIds != null && userGroupIds.size() > 0) {
        if (hasAppend) {
          unionSQL.append(" UNION ");
        }
        unionSQL.append("SELECT OBJECT_ID, PERMISSION ");
        unionSQL.append("FROM   USER_GROUP_PERMISSION ");
        unionSQL.append("WHERE OBJECT_TYPE='"+objectType+"' ");
        unionSQL.append("AND OBJECT_ID=" + objectId + " ");
        unionSQL.append("AND ("+this.getInSQL("USER_GROUP_ID", userGroupIds) + ") ");
        hasAppend = true;
      }
      if (hasAppend) {
        unionSQL.append(" UNION ");
      }
      unionSQL.append("SELECT OBJECT_ID, PERMISSION ");
      unionSQL.append("FROM   USER_RECORD_PERMISSION ");
      unionSQL.append("WHERE OBJECT_TYPE='"+objectType+"' ");
      unionSQL.append("AND OBJECT_ID=" + objectId + " ");
      unionSQL.append("AND USER_RECORD_ID IN (" + userRecordId.toString() + ") ");
      
      unionSQL.append("ORDER BY OBJECT_ID");

      pstmt = dbConn.prepareStatement(unionSQL.toString());

      rs = pstmt.executeQuery();
      String permission = null;
      while (rs.next()) {
          permission = permission!=null ? PermissionManager.combinePermission(rs.getString("PERMISSION"), permission) : rs.getString("PERMISSION");
      }
      return permission==null ? "" : permission;
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
    } catch (Exception e) {
      log.error(e, e);
      throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
    } finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
  
  public Map getDirectPermissionMap(
      String objectType, 
      List objectIds,
      List userRoleIds,
      List userGroupIds,
      Integer userRecordId) throws ApplicationException {
    Map hmResult = new HashMap();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      StringBuffer unionSQL = new StringBuffer();
      boolean hasAppend = false;
      if (userRoleIds != null && userRoleIds.size() > 0) {
        unionSQL.append("SELECT OBJECT_ID, PERMISSION ");
        unionSQL.append("FROM   USER_ROLE_PERMISSION ");
        unionSQL.append("WHERE OBJECT_TYPE='"+objectType+"' ");
        unionSQL.append("AND ("+this.getInSQL("OBJECT_ID", objectIds) + ") ");
        unionSQL.append("AND ("+this.getInSQL("USER_ROLE_ID",userRoleIds) + ") ");
        hasAppend = true;
      }

      if (userGroupIds != null && userGroupIds.size() > 0) {
        if (hasAppend) {
          unionSQL.append(" UNION ");
        }
        unionSQL.append("SELECT OBJECT_ID, PERMISSION ");
        unionSQL.append("FROM   USER_GROUP_PERMISSION ");
        unionSQL.append("WHERE OBJECT_TYPE='"+objectType+"' ");
        unionSQL.append("AND ("+this.getInSQL("OBJECT_ID",objectIds) + ") ");
        unionSQL.append("AND ("+this.getInSQL("USER_GROUP_ID",userGroupIds) + ") ");
        hasAppend = true;
      }
      if (hasAppend) {
        unionSQL.append(" UNION ");
      }
      unionSQL.append("SELECT OBJECT_ID, PERMISSION ");
      unionSQL.append("FROM   USER_RECORD_PERMISSION ");
      unionSQL.append("WHERE OBJECT_TYPE='"+objectType+"' ");
      unionSQL.append("AND ("+this.getInSQL("OBJECT_ID", objectIds) + ") ");
      unionSQL.append("AND USER_RECORD_ID IN (" + userRecordId.toString() + ") ");
      
      unionSQL.append("ORDER BY OBJECT_ID");

      pstmt = dbConn.prepareStatement(unionSQL.toString());

      rs = pstmt.executeQuery();
      while (rs.next()) {
        String oid = objectType+""+rs.getInt("OBJECT_ID");
        if (hmResult.containsKey(oid)) {
          String permission = PermissionManager.combinePermission(rs.getString("PERMISSION"), (String)hmResult.get(oid));
          hmResult.put(oid, permission);
        }else {
          hmResult.put(oid, rs.getString("PERMISSION"));
        }
      }
      return hmResult;
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
    } catch (Exception e) {
      log.error(e, e);
      throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
    } finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
  
  public boolean isInheritPermission(String objectType, Integer objectId) throws ApplicationException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      StringBuffer selSQL = new StringBuffer();
      selSQL.append("SELECT OBJECT_ID ");
      selSQL.append("FROM   USER_PERMISSION_INHERIT ");
      selSQL.append("WHERE  OBJECT_TYPE = ? AND RECORD_STATUS = ? ");
      selSQL.append("AND OBJECT_ID = ? ");
      pstmt = dbConn.prepareStatement(selSQL.toString());
      pstmt.setString(1, objectType);
      pstmt.setString(2, GlobalConstant.RECORD_STATUS_ACTIVE);
      pstmt.setInt(3, objectId.intValue());
      rs = pstmt.executeQuery();
      if (rs.next()) {
        return false;
      }else {
        return true;
      }
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
    } catch (Exception e) {
      log.error(e, e);
      throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
    } finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
  
  public List getNotInheritPermissionObjectIds(String objectType, List objectIds) throws ApplicationException {
    List resultIdList = new ArrayList();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      StringBuffer selSQL = new StringBuffer();
      selSQL.append("SELECT OBJECT_ID ");
      selSQL.append("FROM   USER_PERMISSION_INHERIT ");
      selSQL.append("WHERE  OBJECT_TYPE = ? AND RECORD_STATUS = ? ");
      selSQL.append("AND ("+this.getInSQL("OBJECT_ID", objectIds)+")");
      pstmt = dbConn.prepareStatement(selSQL.toString());
      pstmt.setString(1, objectType);
      pstmt.setString(2, GlobalConstant.RECORD_STATUS_ACTIVE);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        resultIdList.add(new Integer(rs.getInt("OBJECT_ID")));
      }
      return resultIdList;
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
    } catch (Exception e) {
      log.error(e, e);
      throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
    } finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
  
  public List getDmsTreeNodeByIds(List Ids, Integer rootId) throws Exception {
    List lTreeNodes = new ArrayList();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      StringBuffer sql = new StringBuffer();
      sql.append("SELECT ID, PARENT_ID, DOCUMENT_NAME, DOCUMENT_TYPE ");
      sql.append("FROM DMS_DOCUMENT ");
      sql.append("WHERE (RECORD_STATUS = ?) AND (ROOT_ID = ?) AND ("+this.getInSQL("ID", Ids)+") ");
      sql.append("ORDER BY DOCUMENT_TYPE, DOCUMENT_NAME");
      pstmt = dbConn.prepareStatement(sql.toString());
      pstmt.setString(1, GlobalConstant.RECORD_STATUS_ACTIVE);
      pstmt.setInt(2, rootId.intValue());
      rs = pstmt.executeQuery();
      while (rs.next()) {
        DmsTreeNode node = new DmsTreeNode();
        node.setId(new Integer(rs.getInt("ID")));
        node.setParentId(new Integer(rs.getInt("PARENT_ID")));
        node.setDocumentName(rs.getString("DOCUMENT_NAME"));
        node.setDocumentType(rs.getString("DOCUMENT_TYPE"));
        lTreeNodes.add(node);
      }
      return lTreeNodes;
    } catch (Exception ex) {
      throw ex;
    } finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
  
  public List getDmsTreeNodeByParent(Integer parentId, Integer rootId) throws Exception {
    List pid = new ArrayList();
    pid.add(parentId);
    return this.getDmsTreeNodeByParents(pid, rootId);
  }
  
  public List getDmsTreeNodeByParents(List parentIds, Integer rootId) throws Exception {
    List lTreeNodes = new ArrayList();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      StringBuffer sql = new StringBuffer();
      sql.append("SELECT ID, PARENT_ID, DOCUMENT_NAME, DOCUMENT_TYPE ");
      sql.append("FROM DMS_DOCUMENT ");
      sql.append("WHERE (RECORD_STATUS = ?) AND (DOCUMENT_TYPE IN (?, ?, ?)) AND (ROOT_ID = ?) AND ("+this.getInSQL("PARENT_ID", parentIds)+")");
      if(!SystemParameterFactory.getSystemParameterBoolean(SystemParameterConstant.DMS_SHOW_EXPIRE_DOC)){
        sql.append(" AND (((EFFECTIVE_START_DATE IS NULL OR EFFECTIVE_START_DATE <=?) ");
        sql.append(" AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE >?)) ");
        sql.append(" OR OWNER_ID = ?) ");
      }
      sql.append("ORDER BY DOCUMENT_TYPE, DOCUMENT_NAME");
      pstmt = dbConn.prepareStatement(sql.toString());
      pstmt.setString(1, GlobalConstant.RECORD_STATUS_ACTIVE);
      pstmt.setString(2, DmsDocument.FOLDER_TYPE);
      pstmt.setString(3, DmsDocument.COMPOUND_DOC_TYPE);
      pstmt.setString(4, DmsDocument.PAPER_DOC_TYPE);
      pstmt.setInt(5, rootId.intValue());
      if(!SystemParameterFactory.getSystemParameterBoolean(SystemParameterConstant.DMS_SHOW_EXPIRE_DOC)){
        Timestamp currTime = Utility.getCurrentTimestamp();
        pstmt.setTimestamp(6, currTime);
        pstmt.setTimestamp(7, Utility.addDay(currTime, -1));
        pstmt.setInt(8, this.sessionContainer.getUserRecordID().intValue());
      }
      rs = pstmt.executeQuery();
      while (rs.next()) {
        DmsTreeNode node = new DmsTreeNode();
        node.setId(new Integer(rs.getInt("ID")));
        node.setParentId(new Integer(rs.getInt("PARENT_ID")));
        node.setDocumentName(rs.getString("DOCUMENT_NAME"));
        node.setDocumentType(rs.getString("DOCUMENT_TYPE"));
        
        lTreeNodes.add(node);
      }
      return lTreeNodes;
    } catch (Exception ex) {
      throw ex;
    } finally {
      try { rs.close();} catch (Exception ignore) {} finally {rs = null;}
      try { pstmt.close();} catch (Exception ignore) {} finally {pstmt = null;}
    }
  }
    
  private String getInSQL(String fieldName, List lstFieldVals) {
    return this.getSeparatedList( fieldName, lstFieldVals, "IN", "OR", 800 );
  }

  /**
   * Separates the NOT IN clause.
   * To avoid oracle's limitation on expression limit, separate the X.FIELD NOT IN (1,2,3.......) into blocks of
   * (X.FIELD IN (1,2) AND X.FIELD IN (3,4)) ....
   *
   * @param fieldName The field name
   * @param fieldValue The original comma separated value list
   * @return The separated not in clause
   */
  private String getNotInSQL(String fieldName, List lstFieldVals) {
    return this.getSeparatedList ( fieldName, lstFieldVals, "NOT IN", "AND", 800 );
  }

  private String getSeparatedList(String fieldName, List lstFieldVals, String compareOperand, String logicalOperand, int limit) {
    int batchIndex = 0;
    int size = lstFieldVals.size();
    StringBuffer sb = new StringBuffer();
    while (true) {
      int index_start = (batchIndex++) * limit;
      if (index_start>=size) { break; }
      int index_end = (index_start+limit)>size ? size : (index_start+limit);
      if (index_start!=0) sb.append(" "+logicalOperand+" ");
      sb.append(fieldName +" "+compareOperand+" (");
      for (int i=index_start; i<index_end; i++) {
        sb.append((i==index_start) ? lstFieldVals.get(i) : ","+lstFieldVals.get(i));
      }
      sb.append(")");
    }
    return sb.toString();
  }
  
  
}